﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using LibHuman;

namespace Human
{
	public class frmbc_ngoaigio: System.Windows.Forms.Form
	{
        AccessData mDB = new AccessData();
        Language lan = new Language();
        DataSet ds = new DataSet();
        DataSet ds_th = new DataSet();
        DataSet ds_bp = new DataSet();
        DataSet ds_hs = new DataSet();
        string user = "",user_dn="";
        frmReport rpt;
        DataSet dsHd = new DataSet();
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.Label label2;
		private System.Windows.Forms.TextBox txtnam;
		private System.Windows.Forms.Button buttonbcao;
        private System.Windows.Forms.Button buttonkethuc;	
		private string thang,nam,tn;
		private System.Windows.Forms.TextBox txtthang;
		private System.Windows.Forms.Label label5;
		private System.Windows.Forms.ToolTip toolTip1;
        private System.Windows.Forms.ComboBox combodvi;
        private GroupBox groupBox1;
        private ComboBox cboLoainv;
        private Label label3;
        private GroupBox groupKx;
        private RadioButton rd_tonghop;
        private RadioButton rd_chitiet;
		private System.ComponentModel.IContainer components;

		public frmbc_ngoaigio(string s_userdn)
		{			
			InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
            user_dn = s_userdn;
		}
		
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
            this.components = new System.ComponentModel.Container();
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(frmbc_ngoaigio));
            this.label1 = new System.Windows.Forms.Label();
            this.label2 = new System.Windows.Forms.Label();
            this.txtnam = new System.Windows.Forms.TextBox();
            this.buttonkethuc = new System.Windows.Forms.Button();
            this.buttonbcao = new System.Windows.Forms.Button();
            this.txtthang = new System.Windows.Forms.TextBox();
            this.label5 = new System.Windows.Forms.Label();
            this.toolTip1 = new System.Windows.Forms.ToolTip(this.components);
            this.combodvi = new System.Windows.Forms.ComboBox();
            this.groupBox1 = new System.Windows.Forms.GroupBox();
            this.cboLoainv = new System.Windows.Forms.ComboBox();
            this.label3 = new System.Windows.Forms.Label();
            this.groupKx = new System.Windows.Forms.GroupBox();
            this.rd_tonghop = new System.Windows.Forms.RadioButton();
            this.rd_chitiet = new System.Windows.Forms.RadioButton();
            this.groupBox1.SuspendLayout();
            this.groupKx.SuspendLayout();
            this.SuspendLayout();
            // 
            // label1
            // 
            this.label1.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label1.ForeColor = System.Drawing.Color.Navy;
            this.label1.Location = new System.Drawing.Point(28, 20);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(54, 20);
            this.label1.TabIndex = 8;
            this.label1.Text = "Tháng :";
            this.label1.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // label2
            // 
            this.label2.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label2.ForeColor = System.Drawing.Color.Navy;
            this.label2.Location = new System.Drawing.Point(26, 46);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(56, 20);
            this.label2.TabIndex = 9;
            this.label2.Text = "Đơn vị :";
            this.label2.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // txtnam
            // 
            this.txtnam.Font = new System.Drawing.Font("Tahoma", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.txtnam.ForeColor = System.Drawing.Color.Navy;
            this.txtnam.Location = new System.Drawing.Point(182, 20);
            this.txtnam.MaxLength = 4;
            this.txtnam.Name = "txtnam";
            this.txtnam.Size = new System.Drawing.Size(64, 22);
            this.txtnam.TabIndex = 1;
            this.txtnam.TextAlign = System.Windows.Forms.HorizontalAlignment.Center;
            this.txtnam.KeyDown += new System.Windows.Forms.KeyEventHandler(this.txtnam_KeyDown);
            this.txtnam.KeyPress += new System.Windows.Forms.KeyPressEventHandler(this.txtthang_KeyPress);
            // 
            // buttonkethuc
            // 
            this.buttonkethuc.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.buttonkethuc.ForeColor = System.Drawing.Color.Navy;
            this.buttonkethuc.Image = global::Human.Properties.Resources.close_r;
            this.buttonkethuc.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.buttonkethuc.Location = new System.Drawing.Point(132, 141);
            this.buttonkethuc.Name = "buttonkethuc";
            this.buttonkethuc.Size = new System.Drawing.Size(65, 28);
            this.buttonkethuc.TabIndex = 6;
            this.buttonkethuc.Text = "    &Thoát";
            this.toolTip1.SetToolTip(this.buttonkethuc, "Kết thúc");
            this.buttonkethuc.UseVisualStyleBackColor = true;
            this.buttonkethuc.Click += new System.EventHandler(this.buttonkethuc_Click);
            // 
            // buttonbcao
            // 
            this.buttonbcao.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.buttonbcao.ForeColor = System.Drawing.Color.Navy;
            this.buttonbcao.Image = global::Human.Properties.Resources.ok;
            this.buttonbcao.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.buttonbcao.Location = new System.Drawing.Point(61, 141);
            this.buttonbcao.Name = "buttonbcao";
            this.buttonbcao.Size = new System.Drawing.Size(65, 28);
            this.buttonbcao.TabIndex = 4;
            this.buttonbcao.Text = "   &Xem";
            this.toolTip1.SetToolTip(this.buttonbcao, "Báo cáo");
            this.buttonbcao.UseVisualStyleBackColor = true;
            this.buttonbcao.Click += new System.EventHandler(this.xem_Click);
            // 
            // txtthang
            // 
            this.txtthang.Font = new System.Drawing.Font("Tahoma", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.txtthang.ForeColor = System.Drawing.Color.Navy;
            this.txtthang.Location = new System.Drawing.Point(85, 20);
            this.txtthang.MaxLength = 2;
            this.txtthang.Name = "txtthang";
            this.txtthang.Size = new System.Drawing.Size(56, 22);
            this.txtthang.TabIndex = 0;
            this.txtthang.TextAlign = System.Windows.Forms.HorizontalAlignment.Center;
            this.txtthang.KeyDown += new System.Windows.Forms.KeyEventHandler(this.txtthang_KeyDown);
            this.txtthang.KeyPress += new System.Windows.Forms.KeyPressEventHandler(this.txtthang_KeyPress);
            // 
            // label5
            // 
            this.label5.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label5.ForeColor = System.Drawing.Color.Navy;
            this.label5.Location = new System.Drawing.Point(141, 20);
            this.label5.Name = "label5";
            this.label5.Size = new System.Drawing.Size(40, 18);
            this.label5.TabIndex = 11;
            this.label5.Text = " Năm :";
            this.label5.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // combodvi
            // 
            this.combodvi.Font = new System.Drawing.Font("Tahoma", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.combodvi.ForeColor = System.Drawing.Color.Navy;
            this.combodvi.ItemHeight = 14;
            this.combodvi.Location = new System.Drawing.Point(85, 46);
            this.combodvi.Name = "combodvi";
            this.combodvi.Size = new System.Drawing.Size(162, 22);
            this.combodvi.TabIndex = 2;
            this.combodvi.KeyDown += new System.Windows.Forms.KeyEventHandler(this.combodvi_KeyDown);
            // 
            // groupBox1
            // 
            this.groupBox1.Controls.Add(this.groupKx);
            this.groupBox1.Controls.Add(this.cboLoainv);
            this.groupBox1.Controls.Add(this.buttonbcao);
            this.groupBox1.Controls.Add(this.label3);
            this.groupBox1.Controls.Add(this.buttonkethuc);
            this.groupBox1.Controls.Add(this.label5);
            this.groupBox1.Controls.Add(this.label1);
            this.groupBox1.Controls.Add(this.txtthang);
            this.groupBox1.Controls.Add(this.label2);
            this.groupBox1.Controls.Add(this.txtnam);
            this.groupBox1.Controls.Add(this.combodvi);
            this.groupBox1.Location = new System.Drawing.Point(10, 1);
            this.groupBox1.Name = "groupBox1";
            this.groupBox1.Size = new System.Drawing.Size(264, 174);
            this.groupBox1.TabIndex = 7;
            this.groupBox1.TabStop = false;
            // 
            // cboLoainv
            // 
            this.cboLoainv.Font = new System.Drawing.Font("Tahoma", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.cboLoainv.ForeColor = System.Drawing.Color.Navy;
            this.cboLoainv.ItemHeight = 14;
            this.cboLoainv.Location = new System.Drawing.Point(85, 72);
            this.cboLoainv.Name = "cboLoainv";
            this.cboLoainv.Size = new System.Drawing.Size(161, 22);
            this.cboLoainv.TabIndex = 3;
            this.cboLoainv.KeyDown += new System.Windows.Forms.KeyEventHandler(this.cboLoainv_KeyDown);
            // 
            // label3
            // 
            this.label3.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label3.ForeColor = System.Drawing.Color.Navy;
            this.label3.Location = new System.Drawing.Point(2, 76);
            this.label3.Name = "label3";
            this.label3.Size = new System.Drawing.Size(80, 13);
            this.label3.TabIndex = 10;
            this.label3.Text = "Loại nhân viên:";
            this.label3.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // groupKx
            // 
            this.groupKx.Controls.Add(this.rd_tonghop);
            this.groupKx.Controls.Add(this.rd_chitiet);
            this.groupKx.Location = new System.Drawing.Point(41, 100);
            this.groupKx.Name = "groupKx";
            this.groupKx.Size = new System.Drawing.Size(191, 35);
            this.groupKx.TabIndex = 42;
            this.groupKx.TabStop = false;
            this.groupKx.Tag = "0";
            // 
            // rd_tonghop
            // 
            this.rd_tonghop.AutoSize = true;
            this.rd_tonghop.Location = new System.Drawing.Point(111, 12);
            this.rd_tonghop.Name = "rd_tonghop";
            this.rd_tonghop.Size = new System.Drawing.Size(70, 17);
            this.rd_tonghop.TabIndex = 1;
            this.rd_tonghop.Text = "Tổng hợp";
            this.rd_tonghop.UseVisualStyleBackColor = true;
            // 
            // rd_chitiet
            // 
            this.rd_chitiet.AutoSize = true;
            this.rd_chitiet.Checked = true;
            this.rd_chitiet.Location = new System.Drawing.Point(17, 12);
            this.rd_chitiet.Name = "rd_chitiet";
            this.rd_chitiet.Size = new System.Drawing.Size(59, 17);
            this.rd_chitiet.TabIndex = 0;
            this.rd_chitiet.TabStop = true;
            this.rd_chitiet.Text = "Chi tiết";
            this.rd_chitiet.UseVisualStyleBackColor = true;
            // 
            // frmbc_ngoaigio
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 14);
            this.ClientSize = new System.Drawing.Size(279, 176);
            this.Controls.Add(this.groupBox1);
            this.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedDialog;
            this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
            this.Name = "frmbc_ngoaigio";
            this.ShowInTaskbar = false;
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
            this.Text = "Thanh toán ngoài giờ";
            this.Load += new System.EventHandler(this.frmbcttluong_Load);
            this.groupBox1.ResumeLayout(false);
            this.groupBox1.PerformLayout();
            this.groupKx.ResumeLayout(false);
            this.groupKx.PerformLayout();
            this.ResumeLayout(false);

		}
		#endregion

		private void buttonkethuc_Click(object sender, System.EventArgs e)
		{
			this.Close();
		}
		
		private void frmbcttluong_Load(object sender, System.EventArgs e)
		{
            thang = txtthang.Text = DateTime.Now.Month.ToString().PadLeft(2, '0');
            nam = txtnam.Text = DateTime.Now.Year.ToString();
            tn = thang + nam.Substring(2, 2);
			Load_bophan();	
            loadhopdong();
            user = mDB.user;
  		}

        private void loadhopdong()
        {         
            dsHd = mDB.get_nhombc();
            cboLoainv.DataSource = dsHd.Tables[0];
            cboLoainv.DisplayMember = "tennhom";
            cboLoainv.ValueMember = "manhom";            
            cboLoainv.SelectedIndex = -1;
        }
		
		private void Load_bophan()
		{
			DataSet ds=new	DataSet();
			combodvi.DataSource=mDB.get_bophan().Tables[0];
			combodvi.DisplayMember="tenbp";
			combodvi.ValueMember="madv";
			combodvi.SelectedIndex=-1;		
		}
      
        private DataSet get_hesong()
        {
            string sql = " select mcaba_hs as hsn_nt,mcn_hs as hsn_t7,mletet_hs as hsn_lt";
            sql += " ,pcngnt_d as hsd_nt,pcngcn_d as hsd_t7,pcnglt_d as hsd_lt from "+mDB.user+".heso hs";
            sql += " where hs.mmyy= '"+tn+"'";
            return ds_hs = mDB.get_data(sql);
        }
        private DataSet get_data()
        {
            string sql = "", _Mloai = " BC & HD ";     
            sql = "   select '' as bangchu,'"+thang+"' as thangluong,'"+nam+"' as namluong  ,to_char(now(),'dd') as n_ngay,to_char(now(),'mm') as n_thang";
            sql += " , to_char(now(),'yyyy') as n_nam,thcc.manv,trim(nv.ho)||' '|| trim(nv.ten) as hoten,cd.ten,bp.tenbp,bp.madv";
            sql += " ,thcc.nng_ngaythuong as n_nt,thcc.nng_chunhat as n_t7,thcc.nng_letet as n_lt,thcc.dng_ngaythuong as d_nt";
            sql += " ,thcc.dng_chunhat as d_t7,thcc.dng_letet as d_lt";
            sql += " ,thcc.nng_ngaythuong + thcc.nng_chunhat + thcc.nng_letet + thcc.dng_ngaythuong + thcc.dng_chunhat + thcc.dng_letet as tonggio";
            sql += " ,thcc.nng_ngaythuong + thcc.nng_chunhat + thcc.nng_letet as tongngay,thcc.dng_ngaythuong + thcc.dng_chunhat + thcc.dng_letet as tongdem";
            sql += " ,hs.mltt_phucap as luongphucap,hs_nv.h_hslc as hs_lc,hs_nv.h_hscv as hs_cv,hs_nv.h_hsvk as hs_vk,hs_nv.h_hskv as hs_kv,hs.lamviec as lvquydinh,0.0 as luonggio,0.0 as tn_nt,0.0 as tn_t7,0.0 as tn_lt,0.0 as td_nt,0.0 as td_t7,0.0 as td_lt";
            sql += " ,0.0 as t_tongngay,0.0 as t_tongdem,0.0 as t_tongtien";
            sql += " from " + mDB.user + ".llnv nv left join " + mDB.user + ".dmcd cd on nv.macd=cd.ma";
            sql += " inner join " + mDB.user + ".bophan bp on nv.madv=bp.madv inner join " + mDB.user + ".hopdong hd on nv.manv=hd.manv";
            sql += " left join " + mDB.user + ".mahd mh on hd.loaihd=mh.mahd";
            sql += " inner join " + mDB.user + ".th_chamcong_"+ tn +" thcc on nv.manv=thcc.manv ";
            sql += " left join " + mDB.user + ".heso as hs on hs.mmyy=thcc.mmyy";
            sql += " left join " + mDB.user + ".heso_nv_" + tn + " as hs_nv on nv.manv=hs_nv.manv";
            sql += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv ";
            sql += " where (thcc.nng_ngaythuong + thcc.nng_chunhat + thcc.nng_letet + thcc.dng_ngaythuong + thcc.dng_chunhat + thcc.dng_letet)>0";
            sql += " and hs.mmyy=thcc.mmyy and hd.lan=ml.lan";
            if (combodvi.SelectedIndex != -1)
                sql += " and bp.madv='" + combodvi.SelectedValue.ToString() + "'";
            if (cboLoainv.SelectedIndex != -1)
            {
                sql += " and mh.nhombc =" + cboLoainv.SelectedValue.ToString() + " ";
                _Mloai = cboLoainv.Text.ToUpper();
            }
            sql += "  order by bp.madv";            
            ds = mDB.get_data(sql);
            get_hesong();
            decimal hsn_nt = 0, hsn_t7 = 0, hsn_lt = 0, hsd_nt = 0, hsd_t7 = 0, hsd_lt = 0;
            foreach (DataRow r in ds_hs.Tables[0].Rows)
            {
                hsn_nt = decimal.Parse(r["hsn_nt"].ToString());
                hsn_t7 = decimal.Parse(r["hsn_t7"].ToString());
                hsn_lt = decimal.Parse(r["hsn_lt"].ToString());
                hsd_nt = decimal.Parse(r["hsd_nt"].ToString());
                hsd_t7 = decimal.Parse(r["hsd_t7"].ToString());
                hsd_lt = decimal.Parse(r["hsd_lt"].ToString());
            }
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    decimal tn_nt = 0, tn_t7 = 0, tn_lt = 0, td_nt = 0, td_t7 = 0, td_lt = 0, t_tongngay = 0, t_tongdem = 0,t_tongtien=0,luonggio=0;
                    decimal hs_cv = 0, hs_kv = 0,hs_vk=0,hs_lc=0,mltt_pc=0,n_lv=0;
                    if (mDB.h_hscv(tn) == "1")
                    {
                        if (mDB.h_stcv(tn) != "1")
                        {
                            hs_cv =dr["hs_cv"].ToString()!="" ? decimal.Parse(dr["hs_cv"].ToString()):0;
                        }
                    }
                    if (mDB.h_hskv(tn) == "1")
                    {
                        if (mDB.h_stkv(tn) != "1")
                        {
                            hs_kv =dr["hs_kv"].ToString()!="" ? decimal.Parse(dr["hs_kv"].ToString()):0;
                        }
                    }
                    hs_vk=dr["hs_vk"].ToString()!="" ? decimal.Parse(dr["hs_vk"].ToString()):0;
                    hs_lc=dr["hs_lc"].ToString() !="" ? decimal.Parse(dr["hs_lc"].ToString()) :0;
                    mltt_pc=dr["luongphucap"].ToString()!="" ? decimal.Parse(dr["luongphucap"].ToString()):0;
                    n_lv = dr["lvquydinh"].ToString()!="" ? decimal.Parse(dr["lvquydinh"].ToString()):0;
                    if (hs_lc != 0 || hs_vk != 0 || hs_cv != 0 || hs_kv != 0)
                    {
                        luonggio = Math.Round((hs_lc +(hs_lc * hs_vk) + hs_cv + hs_kv) * mltt_pc / n_lv / 8, 0);
                    }
                    else luonggio=0;
                    tn_nt = decimal.Parse(dr["n_nt"].ToString())!=0 ? Math.Round(decimal.Parse(dr["n_nt"].ToString())* luonggio * hsn_nt,2):0;
                    tn_t7 = decimal.Parse(dr["n_t7"].ToString()) != 0 ? Math.Round(decimal.Parse(dr["n_t7"].ToString()) * luonggio * hsn_t7,2) : 0;
                    tn_lt = decimal.Parse(dr["n_lt"].ToString()) != 0 ? Math.Round(decimal.Parse(dr["n_lt"].ToString()) * luonggio * hsn_lt,2) : 0;
                    td_nt = decimal.Parse(dr["d_nt"].ToString()) != 0 ? Math.Round(decimal.Parse(dr["d_nt"].ToString()) * luonggio * hsd_nt,2) : 0;
                    td_t7 = decimal.Parse(dr["d_t7"].ToString()) != 0 ? Math.Round(decimal.Parse(dr["d_t7"].ToString()) * luonggio * hsd_t7,2) : 0;
                    td_lt = decimal.Parse(dr["d_lt"].ToString()) != 0 ? Math.Round(decimal.Parse(dr["d_lt"].ToString()) * luonggio * hsd_lt,2) : 0;
                    t_tongngay = tn_nt + tn_t7 + tn_lt;
                    t_tongdem = td_nt + td_t7 + td_lt;
                    t_tongtien = t_tongngay + t_tongdem;
                    dr["tn_nt"] = tn_nt.ToString();
                    dr["tn_t7"] = tn_t7.ToString();
                    dr["tn_lt"] = tn_lt.ToString();
                    dr["td_nt"] = td_nt.ToString();
                    dr["td_t7"] = td_t7.ToString();
                    dr["td_lt"] = td_lt.ToString();
                    dr["t_tongngay"] = t_tongngay.ToString();
                    dr["t_tongdem"] = t_tongdem.ToString();
                    dr["t_tongtien"] = t_tongtien.ToString();
                    dr["luonggio"] = luonggio.ToString();
                }
            }           
            return ds;
        }
        private DataSet get_data1()
        {           
            ///
            string sql = "",sql1="", _Mloai = " BC & HD ";
            sql = "   select '' as bangchu,'" + thang + "' as thangluong,'" + nam + "' as namluong  ,to_char(now(),'dd') as n_ngay,to_char(now(),'mm') as n_thang";
            sql += " , to_char(now(),'yyyy') as n_nam,thcc.manv,trim(nv.ho)||' '|| trim(nv.ten) as hoten,cd.ten,bp.tenbp,bp.madv";
            sql += " ,thcc.nng_ngaythuong as n_nt,thcc.nng_chunhat as n_t7,thcc.nng_letet as n_lt,thcc.dng_ngaythuong as d_nt";
            sql += " ,thcc.dng_chunhat as d_t7,thcc.dng_letet as d_lt";
            sql += " ,thcc.nng_ngaythuong + thcc.nng_chunhat + thcc.nng_letet + thcc.dng_ngaythuong + thcc.dng_chunhat + thcc.dng_letet as tonggio";
            sql += " ,thcc.nng_ngaythuong + thcc.nng_chunhat + thcc.nng_letet as tongngay,thcc.dng_ngaythuong + thcc.dng_chunhat + thcc.dng_letet as tongdem";
            sql += " ,hs.mltt_phucap as luongphucap,hs_nv.h_hslc as hs_lc,hs_nv.h_hscv as hs_cv,hs_nv.h_hsvk as hs_vk,hs_nv.h_hskv as hs_kv,hs.lamviec as lvquydinh,0.0 as luonggio,0.0 as tn_nt,0.0 as tn_t7,0.0 as tn_lt,0.0 as td_nt,0.0 as td_t7,0.0 as td_lt";
            sql += " ,0.0 as t_tongngay,0.0 as t_tongdem,0.0 as t_tongtien";
            sql += " from " + mDB.user + ".llnv nv left join " + mDB.user + ".dmcd cd on nv.macd=cd.ma";
            sql += " inner join " + mDB.user + ".bophan bp on nv.madv=bp.madv inner join " + mDB.user + ".hopdong hd on nv.manv=hd.manv";
            sql += " left join " + mDB.user + ".mahd mh on hd.loaihd=mh.mahd";
            sql += " inner join " + mDB.user + ".th_chamcong_" + tn + " thcc on nv.manv=thcc.manv ";
            sql += " left join " + mDB.user + ".heso as hs on hs.mmyy=thcc.mmyy";
            sql += " left join " + mDB.user + ".heso_nv_" + tn + " as hs_nv on nv.manv=hs_nv.manv";
            sql += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv ";
            sql += " where (thcc.nng_ngaythuong + thcc.nng_chunhat + thcc.nng_letet + thcc.dng_ngaythuong + thcc.dng_chunhat + thcc.dng_letet)>0";
            sql += " and hs.mmyy=thcc.mmyy and hd.lan=ml.lan";
            if (combodvi.SelectedIndex != -1)
                sql += " and bp.madv='" + combodvi.SelectedValue.ToString() + "'";
            if (cboLoainv.SelectedIndex != -1)
            {
                sql += " and mh.nhombc =" + cboLoainv.SelectedValue.ToString() + " ";
                _Mloai = cboLoainv.Text.ToUpper();
            }
            sql += "  order by bp.madv";
            ds = mDB.get_data(sql);
            sql1 = " select distinct bp.madv,tenbp,0.0 as luonggio,0.0 as gn_nt,0.0 as gn_t7,0.0 as gn_lt,0.0 as gd_nt,0.0 as gd_t7,0.0 as gd_lt ,0.0 as tn_nt,0.0 as tn_t7,0.0 as tn_lt,0.0 as td_nt,0.0 as td_t7,0.0 as td_lt,0.0 as t_tongngay";
            sql1 += " ,0.0 as t_tongdem,0.0 as t_tongtien ";
            sql1 += " from " + user + ".bophan bp inner join "+ user +".th_chamcong_"+tn+" thcc on bp.madv=thcc.madv";
            sql1 += " inner join " + mDB.user + ".hopdong hd on hd.madv=bp.madv";
            sql1 += " inner join " + mDB.user + ".mahd mh on hd.loaihd=mh.mahd";
            sql1 += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv";
            sql1 += " where (thcc.nng_ngaythuong + thcc.nng_chunhat + thcc.nng_letet + thcc.dng_ngaythuong + thcc.dng_chunhat + thcc.dng_letet)>0";
            if (combodvi.SelectedIndex != -1)
                sql1 += " and bp.madv='" + combodvi.SelectedValue.ToString() + "'";
            if (cboLoainv.SelectedIndex != -1)
            {
                sql1 += " and mh.nhombc =" + cboLoainv.SelectedValue.ToString() + " ";
                _Mloai = cboLoainv.Text.ToUpper();
            }
            ds_bp = mDB.get_data(sql1);
            get_hesong();
            decimal hsn_nt = 0, hsn_t7 = 0, hsn_lt = 0, hsd_nt = 0, hsd_t7 = 0, hsd_lt = 0;
            foreach (DataRow r in ds_hs.Tables[0].Rows)
            {
                hsn_nt = decimal.Parse(r["hsn_nt"].ToString());
                hsn_t7 = decimal.Parse(r["hsn_t7"].ToString());
                hsn_lt = decimal.Parse(r["hsn_lt"].ToString());
                hsd_nt = decimal.Parse(r["hsd_nt"].ToString());
                hsd_t7 = decimal.Parse(r["hsd_t7"].ToString());
                hsd_lt = decimal.Parse(r["hsd_lt"].ToString());
            }
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    decimal tn_nt = 0, tn_t7 = 0, tn_lt = 0, td_nt = 0, td_t7 = 0, td_lt = 0, t_tongngay = 0, t_tongdem = 0, t_tongtien = 0, luonggio = 0;
                    decimal hs_cv = 0, hs_kv = 0, hs_vk = 0, hs_lc = 0, mltt_pc = 0, n_lv = 0;
                    if (mDB.h_hscv(tn) == "1")
                    {
                        if (mDB.h_stcv(tn) != "1")
                        {
                            hs_cv = dr["hs_cv"].ToString() != "" ? decimal.Parse(dr["hs_cv"].ToString()) : 0;
                        }
                    }
                    if (mDB.h_hskv(tn) == "1")
                    {
                        if (mDB.h_stkv(tn) != "1")
                        {
                            hs_kv = dr["hs_kv"].ToString() != "" ? decimal.Parse(dr["hs_kv"].ToString()) : 0;
                        }
                    }
                    hs_vk = dr["hs_vk"].ToString() != "" ? decimal.Parse(dr["hs_vk"].ToString()) : 0;
                    hs_lc = dr["hs_lc"].ToString() != "" ? decimal.Parse(dr["hs_lc"].ToString()) : 0;
                    mltt_pc = dr["luongphucap"].ToString() != "" ? decimal.Parse(dr["luongphucap"].ToString()) : 0;
                    n_lv = dr["lvquydinh"].ToString() != "" ? decimal.Parse(dr["lvquydinh"].ToString()) : 0;
                    if (hs_lc != 0 || hs_vk != 0 || hs_cv != 0 || hs_kv != 0)
                    {
                        luonggio = Math.Round((hs_lc + (hs_lc * hs_vk) + hs_cv + hs_kv) * mltt_pc / n_lv / 8, 0);
                    }
                    else luonggio = 0;
                    tn_nt = decimal.Parse(dr["n_nt"].ToString()) != 0 ? Math.Round(decimal.Parse(dr["n_nt"].ToString()) * luonggio * hsn_nt, 2) : 0;
                    tn_t7 = decimal.Parse(dr["n_t7"].ToString()) != 0 ? Math.Round(decimal.Parse(dr["n_t7"].ToString()) * luonggio * hsn_t7, 2) : 0;
                    tn_lt = decimal.Parse(dr["n_lt"].ToString()) != 0 ? Math.Round(decimal.Parse(dr["n_lt"].ToString()) * luonggio * hsn_lt, 2) : 0;
                    td_nt = decimal.Parse(dr["d_nt"].ToString()) != 0 ? Math.Round(decimal.Parse(dr["d_nt"].ToString()) * luonggio * hsd_nt, 2) : 0;
                    td_t7 = decimal.Parse(dr["d_t7"].ToString()) != 0 ? Math.Round(decimal.Parse(dr["d_t7"].ToString()) * luonggio * hsd_t7, 2) : 0;
                    td_lt = decimal.Parse(dr["d_lt"].ToString()) != 0 ? Math.Round(decimal.Parse(dr["d_lt"].ToString()) * luonggio * hsd_lt, 2) : 0;
                    t_tongngay = tn_nt + tn_t7 + tn_lt;
                    t_tongdem = td_nt + td_t7 + td_lt;
                    t_tongtien = t_tongngay + t_tongdem;
                    dr["tn_nt"] = tn_nt.ToString();
                    dr["tn_t7"] = tn_t7.ToString();
                    dr["tn_lt"] = tn_lt.ToString();
                    dr["td_nt"] = td_nt.ToString();
                    dr["td_t7"] = td_t7.ToString();
                    dr["td_lt"] = td_lt.ToString();
                    dr["t_tongngay"] = t_tongngay.ToString();
                    dr["t_tongdem"] = t_tongdem.ToString();
                    dr["t_tongtien"] = t_tongtien.ToString();
                    dr["luonggio"] = luonggio.ToString();
                }
            }
            // gan du lieu theo bo phan
            if (ds.Tables[0].Rows.Count > 0)
            {
                decimal gn_nt = 0, gn_t7 = 0, gn_lt = 0, gd_nt = 0, gd_t7 = 0, gd_lt = 0, tn_nt = 0, tn_t7 = 0, tn_lt = 0, td_nt = 0, td_t7 = 0, td_lt = 0, t_tongngay = 0, t_tongdem = 0, t_tongtien = 0, luonggio = 0;
                foreach (DataRow dr in ds_bp.Tables[0].Rows)
                {
                    DataRow[] dr1 = ds.Tables[0].Select("madv='" + dr["madv"].ToString() + "'", "madv asc");
                    gn_nt = 0; gn_t7 = 0; gn_lt = 0; gd_nt = 0; gd_t7 = 0; gd_lt = 0; tn_nt = 0; tn_t7 = 0; tn_lt = 0; td_nt = 0; td_t7 = 0; td_lt = 0; t_tongngay = 0; t_tongdem = 0; t_tongtien = 0;
                    foreach (DataRow r in dr1)
                    {
                        gn_nt += r["n_nt"].ToString().Trim() != "" ? decimal.Parse(r["n_nt"].ToString()):0;
                        gn_t7 += r["n_t7"].ToString().Trim() != "" ? decimal.Parse(r["n_t7"].ToString()) : 0;
                        gn_lt += r["n_lt"].ToString().Trim() != "" ? decimal.Parse(r["n_lt"].ToString()) : 0;
                        gd_nt += r["d_nt"].ToString().Trim() != "" ? decimal.Parse(r["d_nt"].ToString()) : 0;
                        gd_t7 += r["d_t7"].ToString().Trim() != "" ? decimal.Parse(r["d_t7"].ToString()) : 0;
                        gd_lt += r["d_lt"].ToString().Trim() != "" ? decimal.Parse(r["d_lt"].ToString()) : 0;
                        tn_nt += r["tn_nt"].ToString().Trim() != "" ? decimal.Parse(r["tn_nt"].ToString()) : 0;
                        tn_t7 += r["tn_t7"].ToString().Trim() != "" ? decimal.Parse(r["tn_t7"].ToString()) : 0;
                        tn_lt += r["tn_lt"].ToString().Trim() != "" ? decimal.Parse(r["tn_lt"].ToString()) : 0;
                        td_nt += r["td_nt"].ToString().Trim() != "" ? decimal.Parse(r["td_nt"].ToString()) : 0;
                        td_t7 += r["td_t7"].ToString().Trim() != "" ? decimal.Parse(r["td_t7"].ToString()) : 0;
                        td_lt += dr["td_lt"].ToString().Trim() != "" ? decimal.Parse(r["td_lt"].ToString()) : 0;
                        t_tongngay += r["t_tongngay"].ToString().Trim() != "" ? decimal.Parse(r["t_tongngay"].ToString()) : 0;
                        t_tongdem += r["t_tongdem"].ToString().Trim() != "" ? decimal.Parse(r["t_tongdem"].ToString()) : 0;
                        t_tongtien += r["t_tongtien"].ToString().Trim() != "" ? decimal.Parse(r["t_tongtien"].ToString()) : 0;
                    }
                    dr["gn_nt"] = gn_nt.ToString();
                    dr["gn_t7"] = gn_t7.ToString();
                    dr["gn_lt"] = gn_lt.ToString();
                    dr["gd_nt"] = gd_nt.ToString();
                    dr["gd_t7"] = gd_t7.ToString();
                    dr["gd_lt"] = gd_lt.ToString();
                    dr["tn_nt"] = tn_nt.ToString();
                    dr["tn_t7"] = tn_t7.ToString();
                    dr["tn_lt"] = tn_lt.ToString();
                    dr["td_nt"] = td_nt.ToString();
                    dr["td_t7"] = td_t7.ToString();
                    dr["td_lt"] = td_lt.ToString();
                    dr["t_tongngay"] = t_tongngay.ToString();
                    dr["t_tongdem"] = t_tongdem.ToString();
                    dr["t_tongtien"] = t_tongtien.ToString();
                }                
            }
            return ds_bp;
        }
		private void print(int i)
		{					
			try	{
                get_data();
                get_data1();
		    	ds.WriteXml("..\\xml\\h_ngoaigio_ct.xml",XmlWriteMode.WriteSchema);
                ds_bp.WriteXml("..\\xml\\h_ngoaigio_th.xml", XmlWriteMode.WriteSchema);
				if (ds.Tables[0].Rows.Count>0)
				{
					if(rd_chitiet.Checked)
					{
                        frmReport f = new frmReport(mDB, ds,lan.Change_language_MessageText(" THÁNG ")+ " " +thang +" "+ lan.Change_language_MessageText(" NĂM ") + " " +nam + " ", "h_bc_ngoaigio_ct.rpt",user_dn);
                        f.ShowDialog(this);
					}
				}
                if (ds_bp.Tables[0].Rows.Count > 0)
                {
                    if (rd_tonghop.Checked)
                    {
                        frmReport f = new frmReport(mDB, ds_bp, lan.Change_language_MessageText(" THÁNG ")+ " "+ thang +" "+ lan.Change_language_MessageText(" NĂM ")+" " + nam, "h_bc_ngoaigio_th.rpt",user_dn);
                        f.ShowDialog(this);
                    }
                }
                else
                {
                    MessageBox.Show(lan.Change_language_MessageText("Không có dữ liệu!"), lan.Change_language_MessageText("Thông báo"), MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }

			}
			catch(Exception ex)
			{	
				MessageBox.Show(ex.ToString());
			}
		
		}       
		private void xem_Click(object sender, System.EventArgs e)
		{
			thang=txtthang.Text.Trim().PadLeft(2,'0');
			nam=txtnam.Text;			
			tn=thang+nam.Substring(2,2);
			if(!mDB.bMmyy(tn) )
			{
				MessageBox.Show(lan.Change_language_MessageText("Dữ liệu chưa tạo!"),lan.Change_language_MessageText("Thông báo"));
				return;
			}
			print(0);			
		}      
					
		private void txtnam_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				SendKeys.Send("{Tab}{F4}");
		}

		private void cboloaihd_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				SendKeys.Send("{Tab}");
		}	

		private void combodvi_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				SendKeys.Send("{Tab}{F4}");
		}	

		private void txtthang_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter)
				SendKeys.Send("{Tab}");
		}

        private void txtthang_KeyPress(object sender, KeyPressEventArgs e)
        {
            if ((e.KeyChar < '0' || e.KeyChar > '9') && e.KeyChar != '\b')
                e.KeyChar = '\0';
        }

        private void cboLoainv_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                buttonbcao.Focus();
        }
	}
}
